CREATE PROCEDURE [dbo].[asi_GetCommunitySubscriptions] (
    @groupRoleKey uniqueidentifier,
    @groupTypeKey uniqueidentifier,
    @contactKey uniqueidentifier,
    @communityDocumentKey uniqueidentifier
)
AS
BEGIN

    -- Get the list of all community documents underneath the parent of the specified community document
    DECLARE @communityDocument table
    (
        [DocumentVersionKeyString] nvarchar(40) PRIMARY KEY
    )

    INSERT INTO @communityDocument
        SELECT LOWER(CAST([DocumentVersionKey] AS nvarchar(40)))
          FROM [dbo].[asi_PublishedCommunitiesByDocumentKey](@communityDocumentKey)
         WHERE [DocumentTypeCode] = 'CTY'

    -- Return the subscription information
    SELECT CAST(NULL AS NVARCHAR(100)) AS [Community],
           CAST(NULL AS NVARCHAR(100)) AS [Type],
           CAST(NULL AS NVARCHAR(100)) AS [Title],
           [g].[Name] AS [CommunityDocumentKey],
           [c].[FullName]
      FROM [dbo].[GroupMember] AS [m]
           INNER JOIN [dbo].[GroupMemberDetail] AS [d] ON [m].[GroupMemberKey] = [d].[GroupMemberKey] AND
                                                          [d].[GroupRoleKey] = @groupRoleKey AND [d].[IsActive] = 1 AND
                                                          [d].[GroupMemberStatusCode] = 'A' AND
                                                         ([d].[EffectiveDate] IS NULL OR [d].[EffectiveDate] < GETDATE()) AND
                                                         ([d].[ExpirationDate] IS NULL OR [d].[ExpirationDate] > GETDATE())
           INNER JOIN [dbo].[GroupMain] AS [g] on [m].[GroupKey] = [g].[GroupKey] AND [g].[GroupTypeKey] = @groupTypeKey
           INNER JOIN [dbo].[ContactMain] AS [c] on [m].[MemberContactKey] = [c].[ContactKey]
           INNER JOIN @communityDocument cd ON g.[Name] = cd.[DocumentVersionKeyString]
     WHERE [m].[MemberContactKey] = @contactKey
END


GO